import sqlite3

class DAO:
    REGULAR_MODE = 1
    AD_MODE = 2
    
    "instantiates DAO layer with fileName as db"
    def __init__(self, fileName='userDB.sq3'):
        # create/open database file
        self.m_Connection = sqlite3.connect(fileName)
        self.m_Connection.row_factory = sqlite3.Row
        self.m_Row = None
        
        self.m_Cursor = self.m_Connection.cursor()
        
        #create USER table if it doesn't exist'
        self.m_Cursor.execute(self.scriptCreateTable())
        
        self.m_Connection.isolation_level = None
        self.m_Connection.commit();

    "Returns True if userName exists else False"    
    def userExists(self,userName):
        userExists = False
        self.m_Cursor.execute("SELECT username FROM users WHERE username=?", [userName])
        self.m_Row = self.m_Cursor.fetchone()
        userExists = True if (self.m_Row is not None) else False
        
        self.m_Row = None
        return userExists

    "Returns accesstime for userName if it exists else -1"
    def GetLastAccessedTime(self,userName):
        self.m_Cursor.execute("SELECT accesstime FROM users WHERE username=?",[userName])
        self.m_Row = self.m_Cursor.fetchone()
        
        val = -1 if (self.m_Row is None) else self.m_Row['accesstime']
        self.m_Row = None
        #print "LastAccessedTime for " + userName
        #print val
        return int(val)

    "Updates the timeStamp for userName if it exists. It assumes the userName exists"
    def UpdateLastAccessedTime(self,userName, timeStamp):
        self.m_Cursor.execute("UPDATE users SET accesstime=? WHERE username=?", [timeStamp, userName])
    
    "Script to create the user table"
    def scriptCreateTable(self):
        return "CREATE TABLE IF NOT EXISTS users \
         (username TEXT PRIMARY KEY, accesstime TEXT)"
    
    "Inserts userName and timeStamp into users"     
    def insertData(self, userName, timeStamp):
        self.m_Cursor.execute("INSERT INTO users VALUES \
         (?,?)", [userName, timeStamp])
        
    '''Prints all the data in tables'''
    def printdata(self):
        self.m_Cursor.execute("SELECT * FROM users")
        
        rows = self.m_Cursor.fetchall()
        print "users database info:"
        print "# of rows: ", len(rows)
        for row in rows:
            print row
        

'''                
dao = DAO('myfile')

#dao.insertData('u1', '10');
#dao.insertData('u2', '20');
#dao.insertData('u1', '40');
print "User exists: " + str(dao.userExists('u3'))
dao.GetLastAccessedTime('u3')

dao.printdata()
#dao.UpdateLastAccessedTime('u1', 5)
'''
